<?php

namespace App\Store\Controllers\Financial\Goods;

use App\Common\Services\CommonExcelExport;
use App\Common\Services\Store\Common as StoreCommon;
use App\Store\Models\Account\SiteStore;
use App\Store\Models\Basic\GoodsCategory;
use App\Store\Models\Goods\GoodsStockList;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Maatwebsite\Excel\Facades\Excel;

class GoodsStockHistoryController extends Controller
{
    public function getlist(Request $request)
    {
        $data = $this->getdata($request);
        return response()->json(['code' => '000', 'msg' => '获取成功', 'data' => $data]);
    }

    public function doexport(Request $request)
    {
        $data = $this->getdata($request);
        $store_name = '';

        if (!empty($request['store_id'])) {
            $store_name=SiteStore::where(['id'=>$request['store_id']])->value('store_name');
        }

        $store_ids = StoreCommon::getCurrentAccountStoreId($request);
        if ($store_ids) { //权限控制
            if (is_array($store_ids)) { //获取当前权限下的门店
                foreach ($store_ids as $store_id)
                {
                    $store_name .= SiteStore::where(['id'=>$store_id])->value('store_name').',';
                    $store_name = strstr($store_name,0,-1);
                }
            }
            if (is_numeric($store_ids) && intval($store_ids) > 0) { //获取当前门店下的列表
                $store_name=SiteStore::where(['id'=>$store_ids])->value('store_name');
            }
        }
        $row = [
            ['goods_category_id'=>'历史库存表'],
            ['goods_category_id'=>$store_name.' '.$request['date']],
            [
            'goods_category_id' => '商品类别',
            'total_count' => '总件数',
            'total_piece_weight' => '总件重',
            'total_netglod_weight' => '总金重',
            'total_label_price' => '总标签价',
            'total_cost' => '总成本',
            'goods_style_id' => '款式',
            'count' => '件数',
            'piece_weight' => '件重',
            'netglod_weight' => '金重',
            'label_price' => '标签价',
            'cost' => '成本',
        ]];

        $exceldata = [];
        $initcont = count($row) + 1;
        $mergecell = [];
        foreach ($data as $val) {
            $mergecell[] = 'A'.$initcont.':A'.$initcont+count($val['goods_style_list'])-1;
            $mergecell[] = 'B'.$initcont.':B'.$initcont+count($val['goods_style_list'])-1;
            $mergecell[] = 'C'.$initcont.':C'.$initcont+count($val['goods_style_list'])-1;
            $mergecell[] = 'D'.$initcont.':D'.$initcont+count($val['goods_style_list'])-1;
            $mergecell[] = 'E'.$initcont.':E'.$initcont+count($val['goods_style_list'])-1;
            $mergecell[] = 'F'.$initcont.':F'.$initcont+count($val['goods_style_list'])-1;
            $initcont = $initcont+count($val['goods_style_list']);
            foreach ($val['goods_style_list'] as $k1 => $v) {
                if($k1 == 0)
                {
                    $exceldata[] = [
                        'goods_category_id' => $val['category_name'],
                        'total_count' => $val['count'],
                        'total_piece_weight' => $val['total_piece_weight'],
                        'total_netglod_weight' => $val['total_netglod_weight'],
                        'total_label_price' => $val['total_label_price'] / 100,
                        'total_cost' => $val['total_cost'] / 100,
                        'goods_style_id' => $v['style_name'],
                        'count' => $v['count'],
                        'piece_weight' => $v['total_piece_weight'],
                        'netglod_weight' => $v['total_netglod_weight'],
                        'label_price' => $v['total_label_price'] / 100,
                        'cost' => $v['total_cost'] / 100,
                    ];
                }else{
                    $exceldata[] = [
                        'goods_category_id' => is_numeric($val['category_name'])?0:'',
                        'total_count' => is_numeric($val['count'])?0:'',
                        'total_piece_weight' => is_numeric($val['total_piece_weight'])?0:'',
                        'total_netglod_weight' => is_numeric($val['total_netglod_weight'])?0:'',
                        'total_label_price' => is_numeric($val['total_label_price'])?0:'',
                        'total_cost' => is_numeric($val['total_cost'])?0:'',
                        'goods_style_id' => $v['style_name'],
                        'count' => $v['count'],
                        'piece_weight' => $v['total_piece_weight'],
                        'netglod_weight' => $v['total_netglod_weight'],
                        'label_price' => $v['total_label_price'] / 100,
                        'cost' => $v['total_cost'] / 100,
                    ];
                }

            }
        }

        $filename = '库存历史导出_' . time() . '.xlsx';
        return Excel::download(new CommonExcelExport($row, $exceldata,[],$mergecell), $filename);
    }

    public function getdata(Request $request)
    {
        if (empty($request['date'])) {
            return [];
        }
        $table_name = (new GoodsStockList())->getTable() . '_' . date('Ymd', strtotime($request['date']));
        if (!Schema::hasTable($table_name)) {
            return [];
        }

        $where = [];
        if (!empty($request['store_id'])) {
            $where[$table_name.'.store_id'] =  $request['store_id'];
        }

        if (!empty($request['goods_brand_id'])) {
            $where[$table_name.'.goods_brand_id'] = $request['goods_brand_id'];
        }

        if (!empty($request['goods_style_id'])) {
            $where[$table_name.'.goods_style_id'] = $request['goods_style_id'];
        }

        //多选类别
        $goods_category_id = $request['goods_category_ids'] ?? '';
        $goods_category_ids = [];
        if ($goods_category_id) {
            $goods_category_ids = is_array($goods_category_id) ? $goods_category_id : (is_array(json_decode($goods_category_id, true)) ? json_decode($goods_category_id, true) : []);
        }


        $query = Db::table($table_name)
            ->join('site_basic_goods_category', $table_name . '.goods_category_id', '=', 'site_basic_goods_category.id')
            ->where([
                $table_name . '.is_del' => 1
            ] + $where);
        if (!empty($goods_category_ids)) {
            $query->whereIn($table_name . '.goods_category_id', $goods_category_ids);
        }

        $goods_cabinetgroup_id = intval($request->goods_cabinetgroup_id);
        if($goods_cabinetgroup_id>0){
            $query->whereHas('goods_category', function ($query) use ($table_name,$goods_cabinetgroup_id) {
                $query->where($table_name.'.goods_cabinetgroup_id', $goods_cabinetgroup_id);
            });
        }

        if (!empty($request['store_ids'])) {
            $query->whereIn($table_name . '.store_id', $request['store_ids']);
        }else{
            $store_ids = StoreCommon::getCurrentAccountStoreId($request);
            if ($store_ids) { //权限控制
                if (is_array($store_ids)) { //获取当前权限下的门店
                    $query->whereIn($table_name . '.store_id', $store_ids);
                }
                if (is_numeric($store_ids) && intval($store_ids) > 0) { //获取当前门店下的列表
                    $query->where($table_name . '.store_id', intval($store_ids));
                }
            }
        }

        $query->select($table_name . '.goods_category_id', 'site_basic_goods_category.category_name', $table_name . '.pricetype', DB::raw('sum(count) as count, sum(count*piece_weight) as total_piece_weight, sum(total_netglod_weight) as total_netglod_weight, sum(count*label_price) as total_label_price'));
        $list = $query
            ->where('count','>',0)
            ->groupBy($table_name . '.goods_category_id')
            ->get()
            ->toArray();

        $data = [];
        if ($list) {
            foreach ($list as $k => $item) {
                $data[$k] = json_decode(json_encode($item, true), true);
                $query1 = Db::table($table_name)->where([
                    'is_del' => 1,
                    'goods_category_id' => $item->goods_category_id
                ] + $where);
                $is_gram = GoodsCategory::where(['id' => $item->goods_category_id])->value('is_gram');

                $query1->select('goods_category_id', DB::raw('sum(total_cost_fee) as total_cost'));
                $res1 = $query1->where('count','>',0)->groupBy('goods_category_id')->first();
                $data[$k]['total_cost'] = $res1->total_cost;

                // 款式
                $query2 = Db::table($table_name)
                    ->join('site_basic_goods_style', $table_name . '.goods_style_id', '=', 'site_basic_goods_style.id')
                    ->where([
                        $table_name . '.is_del' => 1,
                        $table_name . '.goods_category_id' => $item->goods_category_id
                    ] + $where);

                $query2->where($table_name.'.count','<>',0);
                $query2->select($table_name . '.goods_style_id', 'site_basic_goods_style.style_name', DB::raw('sum(count) as count, sum(count*piece_weight) as total_piece_weight, sum(total_netglod_weight) as total_netglod_weight, sum(count*label_price) as total_label_price, sum(total_cost_fee) as total_cost'));
                $res2 = $query2->where('count','>',0)
                    ->groupBy($table_name . '.goods_style_id')
                    ->get()
                    ->toArray();
                if ($res2) {
                    foreach ($res2 as $k2 => $v2) {
                        $v2 = json_decode(json_encode($v2, true), true);
                        $data[$k]['goods_style_list'][$k2] = json_decode(json_encode($v2, true), true);

                    }
                }
            }
        }
        return $data;
    }

    public function detail(Request $request)
    {
        $where = [];
        $prefix =env('DB_PREFIX');
        $accountinfo = StoreCommon::getCurrentAccountinfo($request);

        $table_name = (new GoodsStockList())->getTable() . '_' . date('Ymd', strtotime($request['date']));
        $cp_id = $accountinfo['cp_id'];
        $where[$table_name.'.cp_id'] =$cp_id;
        if (!empty($request['goods_style_id'])) {
            $where[$table_name.'.goods_style_id'] = $request['goods_style_id'];
        }

        if (!empty($request['goods_category_id'])) {
            $where[$table_name.'.goods_category_id'] = $request['goods_category_id'];
        }

        $data = Db::table($table_name)
            ->leftJoin('site_basic_goods_category', $table_name . '.goods_category_id', '=', 'site_basic_goods_category.id')
            ->leftJoin('site_basic_goods_style', $table_name . '.goods_style_id', '=', 'site_basic_goods_style.id')
            ->leftJoin('site_store_list', $table_name . '.store_id', '=', 'site_store_list.id')
            ->leftJoin('site_basic_goods_brand', $table_name . '.goods_brand_id', '=', 'site_basic_goods_brand.id')
            ->leftJoin('site_basic_goods_pricetype', $table_name . '.goods_pricetype_id', '=', 'site_basic_goods_pricetype.id')
            ->where([
                    $table_name . '.is_del' => 1,
                ] + $where)
            ->where($table_name.'.count','>',0)
            ->selectRaw("{$prefix}$table_name.*,{$prefix}site_basic_goods_category.category_name,{$prefix}site_basic_goods_style.style_name,{$prefix}site_store_list.store_name,{$prefix}site_basic_goods_brand.brand_name,{$prefix}site_basic_goods_pricetype.pricetype_name")
            ->get()
            ->toArray();

        foreach ($data as &$item){
            $item =(array)$item;
            $item['pricetype_name'] =empty($item['pricetype_name'])?'':$item['pricetype_name'];
            $item['brand_name'] =empty($item['brand_name'])?'':$item['brand_name'];

        }

        return response()->json(['code' => '000', 'msg' => '获取成功', 'data' => $data]);
    }

    public function detailexport(Request $request)
    {
        if(empty($request['date'])){
            return response()->json(['code' => '001', 'msg' => '日期参数不能为空']);
        }
        $accountinfo = StoreCommon::getCurrentAccountinfo($request);
        $table_name = (new GoodsStockList())->getTable() . '_' . date('Ymd', strtotime($request['date']));
        $cp_id = $accountinfo['cp_id'];
        $where[$table_name.'.cp_id'] =$cp_id;
        if (!empty($request['goods_category_id'])) {
            $where[$table_name.'.goods_category_id'] = $request['goods_category_id'];
        }

        if (!empty($request['goods_style_id'])) {
            $where[$table_name.'.goods_style_id'] = $request['goods_style_id'];
        }

        $data = Db::table($table_name)
            ->leftJoin('site_basic_goods_category', $table_name . '.goods_category_id', '=', 'site_basic_goods_category.id')
            ->leftJoin('site_basic_goods_style', $table_name . '.goods_style_id', '=', 'site_basic_goods_style.id')
            ->leftJoin('site_store_list', $table_name . '.store_id', '=', 'site_store_list.id')
            ->leftJoin('site_basic_goods_brand', $table_name . '.goods_brand_id', '=', 'site_basic_goods_brand.id')
            ->leftJoin('site_basic_goods_pricetype', $table_name . '.goods_pricetype_id', '=', 'site_basic_goods_pricetype.id')
            ->where([
                $table_name . '.is_del' => 1,
            ] + $where)
            ->where($table_name.'.count','>',0)
            ->get()
            ->toArray();


        if (!$data) {
            return response()->json(['code' => '001', 'msg' => '导出失败']);
        }

        $row = [
            ['table_header' => '库存历史详情'],
            [
            'goods_sn_code' => '条码',
            'goods_name' => '品名',
            'goods_category_id' => '商品类别',
            'goods_style_id' => '款式',
            'count' => '数量',
            'total_piece_weight' => '重量',
            'total_netglod_weight' => '金重',
            'cf_no' => '证书号',
            's_no' => '款号',
            'c_no' => '圈号',
            'main_stone_weigth' => '主石重',
            'other_stone_weigth' => '副石重',
            'color' => '颜色',
            'clarity' => '净度',
            'label_price' => '标签价',
            'gramprice_fee' => '克销工费',
            'pieceprice_fee' => '件销工费',
            'brand_name' => '品牌',
            'pricetype_name' => '价格类型',
            'store_name' => '所属门店',
            'stockin_at' => '入库时间',
            'create_at' => '创建时间',
        ]];

        $exceldata = [];
        foreach ($data as $val) {

            $val =json_decode(json_encode($val),true);
            $exceldata[] = [
                'goods_sn_code' => $val['goods_sn_code'],
                'goods_name' => $val['goods_name'],
                'goods_category_id' => $val['category_name']??'',
                'goods_style_id' => $val['style_name']??'',
                'count' => $val['count'],
                'total_piece_weight' => $val['total_piece_weight'],
                'total_netglod_weight' => $val['total_netglod_weight'],
                'cf_no' => $val['cf_no'],
                's_no' => $val['s_no'],
                'c_no' => $val['c_no'],
                'main_stone_weigth' => $val['main_stone_weigth']>0?$val['main_stone_weigth']:'',
                'other_stone_weigth' =>$val['other_stone_weigth']>0?$val['other_stone_weigth']:'',
                'color' => $val['color'],
                'clarity' => $val['clarity'],
                'label_price' => $val['label_price'] / 100,
                'gramprice_fee' => $val['gramprice_fee'] / 100,
                'pieceprice_fee' => $val['pieceprice_fee'] / 100,
                'brand_name' => $val['brand_name'] ?? '',
                'pricetype_name' => $val['pricetype_name'] ?? '',
                'store_name' => $val['store_name'] ?? '',
                'stockin_at' =>$val['stockin_at']>0?date('Y-m-d H:i:s', $val['stockin_at']):'',
                'create_at' => $val['create_at']>0?date('Y-m-d H:i:s', $val['create_at']):'',
            ];
        }

        $filename = '库存历史详情导出_' . time() . '.xlsx';
        //未改
        return Excel::download(new CommonExcelExport($row, $exceldata), $filename);
    }
}
